package manager;

/**
 * @author huangjm.2012, mohameds.2012, jaslin.wee.2012, huiwensong.2012, sheryl.tay.2012
 *
 */

import java.sql.SQLException;
import java.util.ArrayList;

import util.ValidationUtil;
import manager.database.DatabaseMgr;
import manager.entitycreator.BidCreator;
import manager.entitycreator.CourseCreator;
import manager.entitycreator.SectionCreator;
import manager.entitycreator.StudentCreator;
import entity.Bid;
import entity.Course;
import entity.Section;
import entity.Student;

public class SectionStudentMgr {

	private DatabaseMgr dbMgr = new DatabaseMgr();
	private SectionCreator sectionCreator = new SectionCreator();
	private StudentCreator studentCreator = new StudentCreator();
	private BidCreator bidCreator = new BidCreator();
	private CourseCreator courseCreator = new CourseCreator();

	public boolean addSectionEnrollmentForStudent(String courseCode,
			String sectionCode, String userId) {

		// insert statement with sub-query to select bidAmount from bid table
		String sql = "insert into section_student "
				+ " (bid_userId, bid_courseCoded, bid_sectionCode, bid_bidAmount, bid_timestamp) "
				+ " values (?, ?, ?, "
				+ " 	("
				+ "			select bid_bidAmount from bid "
				+ " 		where bid_courseCode=? and bid_sectionCode=? and bid_userId=?"
				+ "		), 	"
				+ " 	("
				+ "			select bid_timestamp from bid "
				+ " 		where bid_courseCode=? and bid_sectionCode=? and bid_userId=?"
				+ "		)	) ";

		int numRowsAffected = dbMgr.executeUpdate(sql, courseCode, sectionCode,
				userId, courseCode, sectionCode, userId, courseCode,
				sectionCode, userId);

		return numRowsAffected > 0;
	}

	public int[] addMultipleSectionStudentEnrollments(ArrayList<Bid> bids) {
		String sql = "insert into section_student "
				+ " (bid_userId, bid_courseCode, bid_sectionCode, bid_bidAmount, bid_timestamp) "
				+ " values (?,?,?,?, "
				+ " 	("
				+ "			select bid_timestamp from bid "
				+ " 		where bid_userId=? and bid_courseCode=? and bid_sectionCode=? "
				+ "		) ) ";

		int[] numRowsAffected = { 0 };
		try {
			dbMgr.open();

			dbMgr.setStatement(sql);
			for (Bid eachBid : bids) {
				dbMgr.addBatch(eachBid.getStudent().getUserId(), eachBid
						.getSection().getCourse().getCourseCode(), eachBid
						.getSection().getSectionCode(), eachBid.getBidAmount(),
						eachBid.getStudent().getUserId(), eachBid.getSection()
								.getCourse().getCourseCode(), eachBid
								.getSection().getSectionCode());
			}
			numRowsAffected = dbMgr.executeBatch();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbMgr.close();
		}
		return numRowsAffected;
	}

	public boolean deleteEnrollmentsForSection(String courseCode,
			String sectionCode) {

		String sql = "delete from section_student "
				+ " where bid_courseCode=? and bid_sectionCode=? ";
		int numRowsAffected = dbMgr.executeUpdate(sql, courseCode, sectionCode);

		return numRowsAffected > 0;
	}

	public boolean deleteSectionEnrollmentForStudent(String courseCode,
			String sectionCode, String userId) {

		String sql = "delete from section_student "
				+ " where bid_courseCode=? and bid_sectionCode=? and bid_userId=? ";
		int numRowsAffected = dbMgr.executeUpdate(sql, courseCode, sectionCode,
				userId);

		return numRowsAffected > 0;
	}

	public void reset() {
		dbMgr.executeUpdate("delete from section_student");
	}

	public Bid retrieveSectionEnrollmentForStudent(String courseCode,
			String sectionCode, String userId) {
		String sql = "select * from successful_bids_with_student_section_course_round_with_isopen "
				+ " where bid_courseCode=? and bid_sectionCode=? and bid_userId=? ";
		Bid bid = dbMgr.retrieveEntityForQuery(bidCreator, sql, courseCode,
				sectionCode, userId);

		ValidationUtil.checkForElement(bid);

		return bid;
	}

	public ArrayList<Bid> retrieveAllSuccessfulBids() {
		String sql = "select * from successful_bids_with_student_section_course_round_with_isopen "
				+ " order by bid_courseCode, bid_userId";

		return dbMgr.retrieveMultipleEntitiesForQuery(bidCreator, sql);
	}

	public ArrayList<Bid> retrieveSuccessfulBidsForStudent(String userId) {
		String sql = "select * from successful_bids_with_student_section_course_round_with_isopen "
				+ " where bid_userId=? "
				+ " order by bid_courseCode, bid_userId";

		return dbMgr.retrieveMultipleEntitiesForQuery(bidCreator, sql, userId);
	}

	public ArrayList<Student> retrieveEnrolledStudentsForSection(
			String courseCode, String sectionCode) {

		String sql = "select * from successful_bids_with_student_section_course_round_with_isopen "
				+ " where bid_courseCode=? and bid_sectionCode=? "
				+ " order by bid_courseCode, bid_userId ";

		return dbMgr.retrieveMultipleEntitiesForQuery(studentCreator, sql,
				courseCode, sectionCode);
	}

	public ArrayList<Section> retrieveSectionEnrollmentsForStudent(String userId) {
		String sql = "select * from successful_bids_with_student_section_course_round_with_isopen "
				+ " where bid_userId=? order by bid_courseCode, bid_userId";

		return dbMgr.retrieveMultipleEntitiesForQuery(sectionCreator, sql,
				userId);
	}

	public ArrayList<Bid> retrieveSuccessfulBidsForSection(String courseCode,
			String sectionCode) {

		String sql = "select * from successful_bids_with_student_section_course_round_with_isopen "
				+ " where bid_courseCode=? and bid_sectionCode =? "
				+ " order by bid_courseCode, bid_userId";

		return dbMgr.retrieveMultipleEntitiesForQuery(bidCreator, sql,
				courseCode, sectionCode);
	}

	public ArrayList<Bid> retrieveSuccessfulBidsForSectionAndBidRoundNo(
			String courseCode, String sectionCode, int bidRoundNo) {

		String sql = "select * from successful_bids_with_student_section_course_round_with_isopen "
				+ " where bid_courseCode=? and bid_sectionCode =? and bidRoundNo=? "
				+ " order by bid_courseCode, bid_userId";

		return dbMgr.retrieveMultipleEntitiesForQuery(bidCreator, sql,
				courseCode, sectionCode, bidRoundNo);
	}

	public Bid retrieveSuccessfulBid(String courseCode, String sectionCode,
			String userId) {

		String sql = "select * from successful_bids_with_student_section_course_round_with_isopen "
				+ " where bid_courseCode=? and bid_sectionCode =? and bid_userId =? "
				+ " order by bid_courseCode, bid_userId";

		return dbMgr.retrieveEntityForQuery(bidCreator, sql, courseCode,
				sectionCode, userId);
	}

	public ArrayList<Course> retrieveCourseEnrollmentsForStudent(String userId) {
		String sql = "select * from successful_bids_with_student_section_course_round_with_isopen "
				+ " where bid_userId=? order by bid_courseCode, bid_userId";

		return dbMgr.retrieveMultipleEntitiesForQuery(courseCreator, sql,
				userId);
	}

	public int retrieveNumSuccessfulBidsForSection(String courseCode,
			String sectionCode) {

		String sql = "select count(*) as numSuccessfulBids from section_student where bid_courseCode=? and bid_sectionCode=?";

		return dbMgr
				.retrieveSingleIntegerForQuery(sql, courseCode, sectionCode);
	}

}
